package dbUtils;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;

import entity.Message;

public class DBUtils {

	public static Connection getConn() {

	    String  driver = "com.mysql.jdbc.Driver";
	    String url = "jdbc:mysql://localhost:3306/bb";
	    String username = "root";
	    String password = "12345678";
	    Connection conn = null;
	    try {
	        Class.forName(driver); //classLoader,加载对应驱动
	        conn = (Connection) DriverManager.getConnection(url, username, password);
	    } catch (ClassNotFoundException e) {
	        e.printStackTrace();
			//System.out.println("连接错误");
			//console.log("失败了");
	    } catch (SQLException e) {
			//System.out.println("失败");
	        e.printStackTrace();
	    }
	    return conn;
	}
	
	
	
	public static int insert(String username,String password) {
	    Connection conn = getConn();
	    int i = 0;
	    String sql = "insert into user (username,password) values(?,?)";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	       // pstmt.setInt(1, 2);
	        pstmt.setString(1, username);
	        pstmt.setString(2, password);
	        i = pstmt.executeUpdate();
	        pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
	
	public static Integer getAUser(String username,String password) {
	    Connection conn = getConn();
	    String sql = "select * from user where username=? and password=?";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        pstmt.setString(1, username);
	        pstmt.setString(2, password);
	        ResultSet rs = (ResultSet) pstmt.executeQuery();
	        
	        
	        
	      
	        /*System.out.println("============================");
	        while (rs.next()) {
	        	   System.out.print(rs.getInt("id") + "\t");
	                System.out.print(rs.getString("username") + "\t");
	                System.out.println(rs.getString("password") + "\t");
	             }
	           
	        
	            System.out.println("============================");*/
	        System.out.println("查询数据成功!");
	        
	        if(rs.next()){
	        	return 1;
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return null;
	}
	
	
	public static boolean insertMessage(String username,String subject,String content,String notetime,String ntime){
		
		Connection conn = getConn();
	    String sql = "insert into message (noteuser,subject,content,notetime,ntime) values(?,?,?,?,?)";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        pstmt.setString(1, username);
	        pstmt.setString(2, subject);
	        pstmt.setString(3, content);
	        pstmt.setString(4, notetime);
	        pstmt.setString(5, ntime);
	        int i = pstmt.executeUpdate();
		if(i != 0){
	        System.out.println("留言成功!");
	        return true;
		}
	       
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    		
		return false;
	}
	
	
	public static List<Message> getAllMessage(){
		Connection conn = getConn();
	    String sql = "select * from message";
	    PreparedStatement pstmt;
	    List<Message> list = new ArrayList<Message>();
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	       
	        ResultSet rs = (ResultSet) pstmt.executeQuery();
	        while(rs.next()){
	        	Message message = new Message();
	        	message.setNoteuser(rs.getString("noteuser"));
	        	message.setSubject(rs.getString("subject"));
	        	message.setContent(rs.getString("content"));
	        	message.setNotetime(rs.getDate("notetime"));
	        	message.setNtime(rs.getTime("ntime"));
			    list.add(message);
			}
		     
		    return list;
	       /* while (rs.next()) {
	        	   System.out.print(rs.getString("noteuser") + "\t");
	                System.out.print(rs.getString("subject") + "\t");
	                System.out.println(rs.getString("content") + "\t");
	             }*/
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
		return null;
	}
	
	public static boolean deleteMessage(String notetime){
		Connection conn = getConn();
	    String sql = "delete from message where notetime=?";
	    PreparedStatement pstmt;
		
	    try {
			pstmt = (PreparedStatement)conn.prepareStatement(sql);
			pstmt.setString(1, notetime);
			int re = pstmt.executeUpdate();
			if(re>0){
				return true;
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return false;
	}

}
